package newRegFrm.dao;

import newRegFrm.model.Employee;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class EmployeeDaoImpl implements EmployeeDao{
    @Override
    public int addEmployee(Connection conn, Employee employee) throws SQLException {
        // 检查用户名是否已经存在
        String checkSql = "SELECT empno FROM employee WHERE ename = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(checkSql)) {
            pstmt.setString(1, employee.getEname());
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return 0;
            }
        }

        // 插入新员工
        String sql = "INSERT INTO employee (empno, ename, job, mgr, hiredata, sal, ofee, deptno, role, password) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, employee.getEmpno());
            pstmt.setString(2, employee.getEname());
            pstmt.setString(3, employee.getJob());
            pstmt.setString(4, employee.getMgr());
            pstmt.setDate(5, (Date) employee.getHiredata());
            pstmt.setDouble(6, employee.getSal());
            pstmt.setDouble(7, employee.getOfee());
            pstmt.setInt(8, employee.getDeptno());
            pstmt.setInt(9, employee.getRole() != null ? employee.getRole() : 1);
            pstmt.setString(10, employee.getPassword());

            return pstmt.executeUpdate();
        }
    }

    @Override
    public Employee findByName(Connection conn, String ename) throws SQLException {
        String sql = "SELECT empno, ename, job, mgr, hiredata, sal, ofee, deptno, role, password FROM employee WHERE ename = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, ename);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                Employee employee = new Employee();
                employee.setEmpno(rs.getInt("empno"));
                employee.setEname(rs.getString("ename"));
                employee.setJob(rs.getString("job"));
                employee.setMgr(rs.getString("mgr"));
                employee.setHiredata(rs.getDate("hiredata"));
                employee.setSal(rs.getDouble("sal"));
                employee.setOfee(rs.getDouble("ofee"));
                employee.setDeptno(rs.getInt("deptno"));
                employee.setRole(rs.getInt("role"));
                employee.setPassword(rs.getString("password"));
                return employee;
            }
            return null;
        }
    }

    /*@Override
    public int update(Connection conn, Employee employee) throws SQLException {
        String sql = "UPDATE employee SET empno = ?, job = ?, Mgr = ?, hiredata = ?, sal = ?, ofee = ?, deptno = ? WHERE ename = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, employee.getEmpno());
            pstmt.setString(2, employee.getEname());
            pstmt.setString(3, employee.getJob());
            pstmt.setString(4, employee.getMgr());
            pstmt.setDate(5, (Date) employee.getHiredata());
            pstmt.setDouble(6, employee.getSal());
            pstmt.setDouble(7, employee.getOfee());
            pstmt.setInt(8, employee.getDeptno());
            pstmt.setInt(9, employee.getRole() != null ? employee.getRole() : 1);
            return pstmt.executeUpdate();
        }
    }

    @Override
    public int delete(Connection conn, String ename) throws SQLException {
        String sql = "DELETE FROM employee WHERE ename = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, ename);
             return pstmt.executeUpdate();
        }
    }

    @Override
    public Employee getByName(Connection conn, String ename) throws SQLException {
        String sql = "SELECT * FROM employee WHERE ename = ?";

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, ename);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                Employee employee = new Employee();
                        rs.getInt("empno");
                        rs.getString("ename");
                        rs.getString("job");
                        rs.getString("mgr");
                        rs.getDate("hiredata");
                        rs.getDouble("sal");
                        rs.getDouble("ofee");
                        rs.getInt("deptno");
                        rs.getInt("role");
                        return employee;
            }
            return null;
    }*/

    @Override
    public List<Employee> getAll(Connection conn) {
        List<Employee> employees = new ArrayList<>();
        String sql = "SELECT * FROM employee";

        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                Employee employee = new Employee(
                        rs.getInt("empno"),
                        rs.getString("ename"),
                        rs.getString("job"),
                        rs.getString("mgr"),
                        rs.getDate("hiredata"),
                        rs.getDouble("sal"),
                        rs.getDouble("ofee"),
                        rs.getInt("deptno"),
                        rs.getInt("role"),
                        rs.getString("password")
                );
                employees.add(employee);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return employees;
    }

    @Override
    public Employee login(Connection conn, String ename, String password) throws SQLException {
        String sql = "SELECT * FROM employee WHERE ename = ? AND password = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, ename);
            pstmt.setString(2, password);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                Employee employee = new Employee();
                employee.setEmpno(rs.getInt("empno"));
                employee.setEname(rs.getString("ename"));
                employee.setJob(rs.getString("job"));
                employee.setMgr(rs.getString("mgr"));
                employee.setHiredata(rs.getDate("hiredata"));
                employee.setSal(rs.getDouble("sal"));
                employee.setOfee(rs.getDouble("ofee"));
                employee.setDeptno(rs.getInt("deptno"));
                employee.setRole(rs.getInt("role"));
                employee.setPassword(rs.getString("password"));
                return employee;
            }
            return null;
        }
    }

    @Override
    public Employee getEmployeeByUsername(Connection conn, String username) throws SQLException {
        String sql = "SELECT * FROM employee WHERE username = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, username);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                Employee emp = new Employee();
                emp.setEmpno(rs.getInt("empno"));
                emp.setEname(rs.getString("ename"));
                emp.setJob(rs.getString("job"));
                emp.setMgr(rs.getString("mgr"));
                emp.setHiredata(rs.getDate("hiredata"));
                emp.setSal(rs.getDouble("sal"));
                emp.setOfee(rs.getDouble("ofee"));
                emp.setDeptno(rs.getInt("deptno"));
                emp.setRole(rs.getInt("role"));
                emp.setPassword(rs.getString("password"));
                return emp;
            }
            return null;
        }
    }
}
